package com.ttloner1.preparedstatement;

import com.ttloner1.util.JDBCUtils;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * @author TTLoner
 * @date 2021/6/24 10:16
 * @description
 * 由于insert操作不能批量操作，所以自己实现一个
 * 方式一:使用statement
 * Connection conn=JDBCUtils.getConnection();
 * Statement st=conn.createStatement();
 * for(int i=1;i<20000;i++){
 *     String sql="insert into goods(name)values('name_"+i+"')";
 *     st.execute(sql);
 * }
 */
public class Inserts {
    //方式二，使用preparedStatement,12.91s
    @Test
    public void test() throws SQLException {
        Connection conn = JDBCUtils.getConnection();
        String sql="insert into goods(name) values(?)";
        PreparedStatement ps=conn.prepareStatement(sql);
        for(int i=0;i<20000;i++){
            ps.setString(1,"name_"+(i+1));
            int is = ps.executeUpdate();
            if(is==0) System.out.println("插入错误，第" + (i + 1) + "条");
        }
        JDBCUtils.closeConnection(conn,ps);
    }
    /*
     * @author: TTLoner
     * @time: 2021/6/24 10:48
     * @description:
     * 插入方式三
     *  1.addBatch()、executeBatch()、clearBatch 20000条0.83s 1000000 8.6s
     *  2.在properties配置文件url后加上    ?rewriteBatchedStatements=True
     *  3.使用更新的mysql驱动 mysql-connector-java-5.1.37-bin.jar
     */
    @Test
    public void test2() throws SQLException {
        Connection conn = JDBCUtils.getConnection();
        String sql="insert into goods(name) values(?)";
        PreparedStatement ps=conn.prepareStatement(sql);
        for(int i=0;i<1000000;i++){
            ps.setString(1,"name_"+(i+1));

            //1.攒sql
            ps.addBatch();

            if(i%500==0){
                //执行batch
                ps.executeBatch();

                //清空batch
                ps.clearBatch();
            }
//            if(i==20001){如果存在不整除的情况手动加条件executeBatch
//                ps.executeBatch();
//            }
        }
        JDBCUtils.closeConnection(conn,ps);
    }
    /*
     * @author: TTLoner
     * @time: 2021/6/24 11:11
     * @description:
     *方式四终极版本：在setautocommit处做文章，默认是true，每次excute自动提交，现在我们将所有数据excute后再进行提交
     * 1000000:5s
     */
    @Test
    public void test3() throws SQLException {
        Connection conn = JDBCUtils.getConnection();
        String sql="insert into goods(name) values(?)";
        PreparedStatement ps=conn.prepareStatement(sql);
        //setAutoCommit(false)
        conn.setAutoCommit(false);
        for(int i=0;i<1000000;i++){
            ps.setString(1,"name_"+(i+1));

            //1.攒sql
            ps.addBatch();

            if(i%500==0){
                //执行batch
                ps.executeBatch();

                //清空batch
                ps.clearBatch();
            }
//            if(i==20001){如果存在不整除的情况手动加条件executeBatch
//                ps.executeBatch();
//            }
            //一次提交
        }
        conn.commit();
        JDBCUtils.closeConnection(conn,ps);
    }
}
